package intranet.models;
import java.sql.*;
import java.util.ArrayList;
public class ViewAllFileModel {
	Connection DBConnection=null;
	public ViewAllFileModel(){
		DatabaseModel DBModel=new DatabaseModel();
		DBConnection=DBModel.GetConnection();
	}
	
	/* Get accessible file - return as Array Integer */
	public int[] getAccessibleFile(int user_id){
		int[] arrfile=null;
		ResultSet rst=null;
		try{
			Statement ConStatement=DBConnection.createStatement(
	                ResultSet.TYPE_SCROLL_INSENSITIVE,
	                ResultSet.CONCUR_UPDATABLE);
			String Sql="SELECT FILE_ID FROM FILE_ACCESSIBLE_USERS WHERE USER_ID="+user_id;
			rst=ConStatement.executeQuery(Sql);
			
			// Get ResultSet Length
			int arrsize=0;
			while(rst.next()){
				arrsize++;
			}
			arrfile=new int[arrsize];
			
			//Add user id to array
			int i=0;
			rst.beforeFirst();
			while(rst.next()){
				arrfile[i]=rst.getInt("FILE_ID");
				i++;
			}
			
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return arrfile;
	}
	
	
	/* Get accessible file FROM Sharing Group - return as Array Integer */
	public int[] getAccessibleFileByGroup(int user_id){
		int[] arruser=null;
		ResultSet rst=null;
		try{
			Statement ConStatement=DBConnection.createStatement(
	                ResultSet.TYPE_SCROLL_INSENSITIVE,
	                ResultSet.CONCUR_UPDATABLE);
			String Sql="SELECT G.GROUP_ID,G.FILE_ID,M.USER_ID FROM FILE_ACCESSIBLE_GROUPS G INNER JOIN GROUP_MEMBERS M ON G.GROUP_ID=M.GROUP_ID WHERE M.USER_ID="+ user_id;
			rst=ConStatement.executeQuery(Sql);
			
			// Get ResultSet Length
			int arrsize=0;
			while(rst.next()){
				arrsize++;
			}
			arruser=new int[arrsize];
			
			//Add user id to array
			int i=0;
			rst.beforeFirst();
			while(rst.next()){
				arruser[i]=rst.getInt("FILE_ID");
				i++;
			}
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return arruser;
	}
	
	/* File Sharing / View All File */
	public ResultSet ViewAllFiles(int cat_id,int user_id){
	ResultSet result=null;
	String Sql=null;
	
	// Generate WHERE FILE_ID CONDITION
	String condition="";
	int[] arrbyuser=this.getAccessibleFile(user_id);
	int[] arrbygroup=this.getAccessibleFileByGroup(user_id);
	
	/* By USER */
	if(arrbyuser.length>0){
		for(int i=0;i<=arrbyuser.length-1;i++){
			condition+=" OR FILE_ID="+arrbyuser[i];
		}
	}
	
	/* By GROUP */
	if(arrbygroup.length>0){
		for(int j=0;j<=arrbygroup.length-1;j++){
			condition+=" OR FILE_ID="+arrbygroup[j];
		}
	}
	
	
	// ArrayList userList1=this.
	try{
			Statement ConStatement=DBConnection.createStatement();
	if (cat_id==0){
		
			Sql="SELECT FILE_ID,FILE_TITLE," +
							  "FILE_NAME," +
							  "DBMS_LOB.SUBSTR(FILE_DESC,DBMS_LOB.GETLENGTH(FILE_DESC),1) AS FILE_DESC," +
							  "FILE_SIZE,FILE_PATH," +
							  "FREQUENCY FROM UPLOADED_FILES WHERE USER_UPLOAD="+user_id+" OR IS_PUBLIC=1"+condition;
			result=ConStatement.executeQuery(Sql);
	}else{
	
		Sql="SELECT FILE_ID,FILE_TITLE," +
				  "FILE_NAME," +
				  "DBMS_LOB.SUBSTR(FILE_DESC,DBMS_LOB.GETLENGTH(FILE_DESC),1) AS FILE_DESC," +
				  "FILE_SIZE,FILE_PATH, " +
				  "FREQUENCY FROM UPLOADED_FILES " +
				  "WHERE (CATEGORY_ID="+cat_id+") AND (USER_UPLOAD="+user_id+" OR IS_PUBLIC=1"+condition+")";	
		result=ConStatement.executeQuery(Sql);
			
	}
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}	
	
	public ResultSet SelectDetailFile(int cat_id){
		ResultSet result=null;
		String Sql="";
		Sql="SELECT UPLOADED_FILES.FILE_ID," +
				  "UPLOADED_FILES.FILE_NAME," +
				  "UPLOADED_FILES.FILE_TITLE," +
				  "DBMS_LOB.SUBSTR(UPLOADED_FILES.FILE_DESC,DBMS_LOB.GETLENGTH(UPLOADED_FILES.FILE_DESC),1) AS FILE_DESC," +
				  "UPLOADED_FILES.FILE_SIZE," +
				  "UPLOADED_FILES.FILE_CATEGORY," +
				  "UPLOADED_FILES.FREQUENCY," +
				  "USER_GROUPS.GROUP_NAME FROM " +
				  "UPLOADED_FILES INNER JOIN USER_GROUPS " +
				  "ON USER_GROUPS.GROUP_ID=UPLOADED_FILES. " +
				  "WHERE UPLOADED_FILES.CATEGORY_ID="+cat_id+"";	
		try{
			Statement ConStatement=DBConnection.createStatement();
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}	
}
